/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.ProfileDao;
import com.tesam.gedeon.entity.Profile;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author joelpacheco
 */
@Repository
public class ProfileDaoImpl extends SimpleJdbcDaoSupport implements ProfileDao {

    @Autowired
    public ProfileDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int save(Profile profile) {
        getJdbcTemplate().update("INSERT INTO profile"
                + "("
                + "createdBy,dateCreated,status,deleted,name,descrption"
                + ")VALUES(?,NOW(),?,?,?,?,?)", profile.getCreatedBy(), 1, 0, profile.getName(), profile.getDescription());

        return getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
    }

    @Override
    public int delete(int id) {
        return getJdbcTemplate().update("UPDATE profile SET deleted = 1 WHERE id = ?", id);
    }

    @Override
    public int update(Profile profile) {
        return getJdbcTemplate().update("UPDATE profile SET modifiedby = ?,datemodified = NOW(), name = ?,description = ? WHERE id = ?",
                profile.getCreatedBy(), profile.getName(), profile.getDescription(), profile.getId());
    }

    @Override
    public Profile getById(int id) {
        return getSimpleJdbcTemplate().queryForObject("SELECT * FROM profile WHERE id = ?", new BeanPropertyRowMapper<Profile>(Profile.class), id);
    }

    @Override
    public List<Profile> getAll() {
        return getSimpleJdbcTemplate().query(
                "SELECT id,createdby,modifiedby,datecreated,datemodified,status,name,description FROM profile",
                new BeanPropertyRowMapper<Profile>(Profile.class));
    }

    @Override
    public List<Profile> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        String query = null;
        try {

            query = getLimit("SELECT id,createdby,modifiedby,datecreated,datemodified,status,name,description FROM profile", startLimit, endLimit, dataToSort, directionOfOrder);
            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Profile>(Profile.class));

        } catch (Exception e) {
            System.out.println("getAllByLimit : Exception :" + e.getMessage());
            return null;
        }
    }

    private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }

    @Override
    public List<Profile> searchByName(Profile profile, int satartLimit, int endLimit) {
        String query = null;
        try {
            query = getLimit("SELECT id,createdby,modifiedby,datecreated,datemodified,status,name,description FROM profile WHERE upper(name) LIKE ('%'||?||'%')", satartLimit, endLimit, "name", "ASC");
            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Profile>(Profile.class), profile.getName());
        } catch (Exception e) {
            System.out.println("searchByName:Exception:" + e.getMessage());
            return null;
        }
    }

    @Override
    public int getCountSearchByName(Profile profile) {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM profile WHERE upper(name) LIKE ('%' || ? || '%')", profile.getName());
    }

    @Override
    public int getCountTotal() {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM profile");
    }

    @Override
    public List<Profile> getAllByRolId(int rolId) {
        return getSimpleJdbcTemplate().query("SELECT pf.id,pf.name FROM rolprofile rp INNER JOIN profile pf "
                + " ON rp.profileid = pf.id WHERE rp.rolid = ?", new BeanPropertyRowMapper<Profile>(Profile.class), rolId);
    }
}
